from mydb.odbc import atable
from mydb.oracle import base as orabase

#odbc=base()
#odbc.dsn='payroll'
#odbc.charset='cp866'
#gen=odbc.generate('SELECT * FROM Anketa')
#odbc.commit('CREATE TABLE E:\Py\MyPy\progs\payroll\test.dbf (test varchar(10))')

oracle = orabase()

def read_norms(year, month):
	"This function read signs' norma data"
	recs = oracle.generate("SELECT SIGN, NORMA FROM PALO_NZP WHERE YEAR="+str(year)+" AND MONTH="+str(month))
	result={}
	for rec in recs:
		result[rec[0]]=rec[1]
	return result

def read(year, month):
	"This function reads data from oracle"
	manuals = read_norms(year, month)
	l_manuals = len(manuals)
	#recs = oracle.fetch('''SELECT '110' AS "ceh", PALO_NZP.WSCODE AS "uch", PALO_NZP.SHIFR_Z AS "zak", SUBSTR(PALO_NZP.SIGN,1,28) AS "nchert", PALO_NZP.QUANT AS "kol_det",  ma.PLCODE as "nomnom", '' AS "n_oper", PALO_NZP.OPER5 AS "n_opr5", PALO_NZP.NORMA AS "normar", 0 AS "chist", 0 AS "chern", 0 AS "tst", 0 AS "tpz", 0 AS "cena", '' AS "mar", TO_CHAR(PALO_NZP.YEAR) AS "god", TO_CHAR(PALO_NZP.MONTH) AS "mes", '' AS "prbl" FROM KONSTROBJ ko, (SELECT a.* FROM DET_EXPENSE a, (SELECT DETCODE, MIN(NORMSTATUS)AS "NORMSTATUS" FROM DET_EXPENSE WHERE STYLE_EX=1 GROUP BY(DETCODE)) b WHERE a.DETCODE=b.DETCODE AND a.NORMSTATUS=b.NORMSTATUS AND a.STYLE_EX=1) de, MEASURES me, MATERIALS ma, OBJ_ATTR_VALUES_1000000 oav, palo_nzp WHERE de.PUREWEIGHTMEAS=me.CODE and ko.UNVCODE=de.DETCODE and de.MATCODE=ma.CODE and oav.SOCODE=de.SOCODE and ko.SIGN=palo_nzp.SIGN ORDER BY PALO_NZP.WSCODE, PALO_NZP.SHIFR_Z, palo_nzp.sign asc''')
	
	
	query='''SELECT 

    '110' AS "ceh", PALO_NZP.WSCODE AS "uch", PALO_NZP.SHIFR_Z AS "zak", 
    SUBSTR(PALO_NZP.SIGN,1,28) AS "nchert", 
    PALO_NZP.QUANT AS "kol_det",  
    ma.PLCODE as "nomnom", 
    b.OPERCODE AS "n_oper", 
    PALO_NZP.OPER5 AS "n_opr5", 
    de.FULLNORM1 AS "normar", 
    de.PUREWEIGHT as "chist",
    de.WASTE_ALL + de.PUREWEIGHT as "chern", 
    b.UNITTIME/60 AS "tst", b.BATCHTIME/60 AS "tpz", 
    0 AS "cena", 
    '' AS "mar", 
    TO_CHAR(PALO_NZP.YEAR) AS "god", TO_CHAR(PALO_NZP.MONTH) AS "mes", 
    '' AS "prbl" 

FROM 

    KONSTROBJ ko, 

    (SELECT a.* 
        FROM 
            DET_EXPENSE a, 
            (SELECT 
                DETCODE, MIN(NORMSTATUS)AS "NORMSTATUS" 
            FROM 
                DET_EXPENSE 
            WHERE STYLE_EX=1 
            GROUP BY(DETCODE)) b 
        WHERE 
            a.DETCODE=b.DETCODE 
            AND a.NORMSTATUS=b.NORMSTATUS 
            AND a.STYLE_EX=1) de, 
    
    MEASURES me, 
    MATERIALS ma, 
    OBJ_ATTR_VALUES_1000000 oav, 
    palo_nzp 
    LEFT JOIN 

    (

    SELECT 
    ko.SIGN, b.OPERNUM, b.SHORTNAME as "Участок", 
    top.OPERCODE, b.CATEGORY as "Разряд", me.CODE_BMN as " Единица измерения", 
    tof.UNITTIME, tof.BATCHTIME

FROM 
    KONSTROBJ ko, TECHNOLOGY_OPERATIONS top, TECHPROCESSES tp,
    TECHOPERATION_TIME_RATES tof, MEASURES me, 

    (SELECT
        a.CODE_OFT, a.OPERNUM, a.TECH_PROCESSES_CODE, a.TECH_OPER_CODE, a.SECTIONS_CODE,
        a.CODE, a.SHORTNAME, tpop.CATEGORY
    FROM
        (SELECT 
            oft.CODE as "CODE_OFT", oft.OPERNUM, oft.TECH_PROCESSES_CODE,
            oft.TECH_OPER_CODE, oft.SECTIONS_CODE, s.CODE, s.SHORTNAME  
        FROM 
            OPERATIONS_FOR_TECHPROCESSES oft
            LEFT JOIN SECTIONS s ON oft.SECTIONS_CODE=s.CODE) a
        
        LEFT JOIN TPOPER_PERFORMERS tpop ON tpop.TP_OPER_CODE=a.CODE_OFT) b,


    (SELECT a.UNVCODE, bobj.DOCCODE AS "TPCODE"

        FROM

            (SELECT 

                tptk.UNVCODE, MAX(bp.CODE) AS "CODE"

            FROM OMP_ADM.BUSINESSOBJ_PROMOTION bp,
                OMP_ADM.BUSINESS_OBJECTS bobj,
                OMP_ADM.TECHPROCESSES tp,
                TECHPROC_TO_KOBJ tptk
                
            WHERE
                bp.CURRENT_STATE=102
                AND bobj.CODE=bp.BUSINESSOBJ
                AND bobj.DOCCODE=tp.CODE
                AND tp.CODE=tptk.TPCODE
                        
            GROUP BY (tptk.UNVCODE))a,

            OMP_ADM.BUSINESSOBJ_PROMOTION bp,
            OMP_ADM.BUSINESS_OBJECTS bobj

        WHERE bp.CODE=a.CODE
            AND bobj.CODE=bp.BUSINESSOBJ) tptk

WHERE b.TECH_PROCESSES_CODE=tp.CODE
    AND b.TECH_OPER_CODE=top.CODE
    AND tof.OPER_FOR_TECH_CODE=b.CODE_OFT
    AND ko.UNVCODE=tptk.UNVCODE
    AND tp.CODE=tptk.TPCODE
    AND ko.MEASCODE=me.CODE
    AND tp.ARCHIVE!=1

    ) b ON b.SIGN=palo_nzp.SIGN AND b.OPERNUM=palo_nzp.OPER5,


    (

    SELECT a.SIGN, bobj.DOCCODE AS "UNVCODE"

        FROM

            (SELECT 

                ko.SIGN, MAX(bp.CODE) AS "CODE"

            FROM OMP_ADM.BUSINESSOBJ_PROMOTION bp,
                OMP_ADM.BUSINESS_OBJECTS bobj,
                OMP_ADM.KONSTROBJ ko
                
            WHERE
                bp.CURRENT_STATE=5165
                AND bobj.CODE=bp.BUSINESSOBJ
                AND bobj.DOCCODE=ko.UNVCODE
                AND ko.ITEMTYPE=2
                        
            GROUP BY (ko.SIGN))a,

            OMP_ADM.BUSINESSOBJ_PROMOTION bp,
            OMP_ADM.BUSINESS_OBJECTS bobj

        WHERE bp.CODE=a.CODE
            AND bobj.CODE=bp.BUSINESSOBJ

    ) c




WHERE 
    de.PUREWEIGHTMEAS=me.CODE
    and ko.UNVCODE=de.DETCODE 
    and de.MATCODE=ma.CODE 
    and oav.SOCODE=de.SOCODE 
    and c.SIGN=palo_nzp.SIGN 
    and c.UNVCODE=ko.UNVCODE


ORDER BY PALO_NZP.WSCODE, PALO_NZP.SHIFR_Z, PALO_NZP.SIGN asc'''
	
	
	recs = oracle.fetch(query)
	#recs = oracle.fetch('''SELECT DISTINCT '110' AS "ceh", PALO_NZP.WSCODE AS "uch",  PALO_NZP.SHIFR_Z AS "zak", SUBSTR(PALO_NZP.SIGN,1,28) AS "nchert", PALO_NZP.QUANT AS "kol_det",  ma.PLCODE as "nomnom", '' AS "n_oper", PALO_NZP.OPER5 AS "n_opr5", PALO_NZP.NORMA AS "normar", 0 AS "chist", 0 AS "chern", 0 AS "tst", 0 AS "tpz", 0 AS "cena", '' AS "mar", TO_CHAR(PALO_NZP.YEAR) AS "god", TO_CHAR(PALO_NZP.MONTH) AS "mes", '' AS "prbl" FROM KONSTROBJ ko, (SELECT a.* FROM DET_EXPENSE a, (SELECT DETCODE, MIN(NORMSTATUS)AS "NORMSTATUS" FROM DET_EXPENSE WHERE STYLE_EX=1 GROUP BY(DETCODE)) b WHERE a.DETCODE=b.DETCODE AND a.NORMSTATUS=b.NORMSTATUS AND a.STYLE_EX=1) de, MEASURES me, MATERIALS ma, OBJ_ATTR_VALUES_1000000 oav, palo_nzp WHERE de.PUREWEIGHTMEAS=me.CODE and ko.UNVCODE=de.DETCODE and de.MATCODE=ma.CODE and oav.SOCODE=de.SOCODE and ko.SIGN=palo_nzp.SIGN''')
	print('В no_job.DBF будет записано лишних записей: '+str(len(recs)-l_manuals))
	return recs


#Works too long
#def cena(nom_nom):
#	"This function returns price for the nom_nom"
#	cen_pc=atable()
#	cen_pc.table.base.dsn='alldata'
#	cen_pc.name='cen_pc'
#	
#	print(cen_pc.select(['cena'],[['nom_nom', '=', "'"+nom_nom+"'"]])[0][0])
#	result=float(cen_pc.select(['cena'],[['nom_nom', '=', "'"+nom_nom+"'"]])[0][0])
#	return result

#Error - sql statement too long
#def cennik(recs):
#	"This function returns price for the recs"
#	noms=[]
#	for rec in recs:
#		noms+=[rec[5]]
#	clause=[['nom_nom', '=', "'"+noms[0]+"'"]]
#	for nom in noms[1:]:
#		clause+=[['OR nom_nom', '=', "'"+nom+"'"]]
#	
#	print(clause)
#	
#	cen_pc=atable()
#	cen_pc.table.base.dsn='alldata'
#	cen_pc.name='cen_pc'
#
#	select=cen_pc.select(['nom_nom', 'cena'], clause)
#	result={}
#	for row in select:
#		result[row[0]]=row[1]
#
#	return result

#print(cennik(read(2013,11)))

def write(recs):
	"This function writes data to no_job.DBF"
	no_job=atable()
	#no_job.table.base.dsn='cen'
	#no_job.table.base.dsn='umc'
	#no_job.table.base.netdrive='V:'
	no_job.name='no_job'
	
	#no_job.fields=[
	#['ceh','c (3)','', ''],
	#['uch','c (3)','', ''],
	#['zak','c (7)','', ''],
	#['nchert','c (28)','', ''],
	#['kol_det','n (3)','', ''],
	#['nomnom','c (10)','', ''],
	#['n_oper','c (3)','', ''],
	#['n_opr5','c (3)','', ''],
	#['normar','n (10,4)','', ''],
	#['chist','n (10,4)','', ''],
	#['chern','n (10,4)','', ''],
	#['tst','n (10,3)','', ''],
	#['tpz','n (10,3)','', ''],
	#['cena','n (10,2)','', ''],
	#['mar','c (45)','', ''],
	#['god','c (4)','', ''],
	#['mes','c (2)','', ''],
	#['prbl','c (1)','', '']
	#]
	#no_job.create()
	no_job.table.base.connect()
	no_job.table.base.cursor.execute("DELETE FROM V://no_job")
	no_job.table.base.conn.commit()
	
	cen_pc=atable()
	cen_pc.table.base.dsn='alldata'
	cen_pc.name='cen_pc'
	cen_pc.table.base.connect()
	
	#temp2=[]
	count=0
	for rec in recs:
		#if rec not in temp2:
		count+=1
		print(count, rec[1], rec[2], rec[3], rec[5])
		#temp2+=[rec]
		temp=[]
		for x in range(len(rec)):
			if rec[x]!=None:
				temp+=[rec[x]]
			elif x in [8,9,10,11,12]:
				temp+=[0]
			else:
				temp+=['']
		cena=float(cen_pc.table.base.fetch("SELECT Cena FROM cen_pc WHERE nom_nom="+"'"+temp[5]+"'")[0][0])
		#print("insert into no_job(ceh, uch, zak, nchert, kol_det, nomnom, n_oper, n_opr5, normar, chist, chern, tst, tpz, cena, mar, god, mes, prbl) values ('"+str(temp[0])+"','"+str(temp[1])+"','"+str(temp[2])+"','"+str(temp[3])+"',"+str(temp[4])+",'"+str(temp[5])+"','"+str(temp[6])+"','"+str(temp[7])+"',"+str(temp[8])+","+str(temp[9])+","+str(temp[10])+","+str(temp[11])+","+str(temp[12])+","+str(cena(temp[5]))+",'"+str(temp[14])+"','"+str(temp[15])+"','"+str(temp[16])+"','"+str(temp[17])+"')")
		no_job.table.base.cursor.execute("insert into V://no_job (ceh, uch, zak, nchert, kol_det, nomnom, n_oper, n_opr5, normar, chist, chern, tst, tpz, cena, mar, god, mes, prbl) values ('"+str(temp[0])+"','"+str(temp[1])+"','"+str(temp[2])+"','"+str(temp[3])+"',"+str(temp[4])+",'"+str(temp[5])+"','"+str(temp[6])+"','"+str(temp[7])+"',"+str(temp[8])+","+str(temp[9])+","+str(temp[10])+","+str(temp[11])+","+str(temp[12])+","+str(cena)+",'"+str(temp[14])+"','"+str(temp[15])+"','"+str(temp[16])+"','"+str(temp[17])+"')")

		no_job.table.base.conn.commit()
	cen_pc.table.base.disconnect()
	no_job.table.base.disconnect()

write(read(2013,11))